05 바인드 변수의 중요성

SQL> CREATE TABLE t AS SELECT * FROM all_objects;

테이블이 생성되었습니다.

SQL> UPDATE t SET object_id = ROWNUM;

6989 행이 갱신되었습니다.

SQL> CREATE UNIQUE INDEX t_idx ON t(object_id);

인덱스가 생성되었습니다.

SQL> ANALYZE TABLE t COMPUTE STATISTICS;

테이블이 분석되었습니다.

SQL> SET AUTOT TRACEONLY EXPLAIN

SQL> SELECT object_name FROM t WHERE object_id = 1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2929955852

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    20 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=1000)

SQL> SET AUTOT OFF

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

SQL> SET TIMING ON
SQL> DECLARE
  2    TYPE rc IS REF CURSOR;
  3    l_rc rc;
  4    l_object_name t.object_name%TYPE;
  5  BEGIN
  6    FOR i IN 1 .. 20000
  7    LOOP
  8      OPEN l_rc FOR
  9        'SELECT /* test1 */ object_name
 10           FROM t
 11          WHERE object_id = :x' USING i;
 12      FETCH l_rc INTO l_object_name;
 13      CLOSE l_rc;
 14    END LOOP;
 15  END;
 16  /

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:00.96
SQL> SELECT sql_text
  2       , loads
  3       , parse_calls
  4       , executions
  5       , fetches
  6    FROM v$sql
  7   WHERE sql_text LIKE '%test1%'
  8     AND sql_text NOT LIKE '%v$sql%'
  9     AND sql_text NOT LIKE '%DECLARE%'
 10  ;

SQL_TEXT                        LOADS PARSE_CALLS EXECUTIONS FETCHES
------------------------------- ----- ----------- ---------- -------
SELECT ... WHERE object_id = :x     1       20000      20000   20000

경   과: 00:00:00.04

1. 1. 하드파싱 1회에 20000회 실행 : 커서 공유 확인
   2. PARSE_CALLS이 20000회 발생된 이유는 Dynamic Sql 을 사용했기 때문
   3. Static Sql 을 사용했다면 PARSE_CALLS 1회만 발생.

• 리터럴 상수 테스트
  
  
  

SQL> DECLARE
  2    TYPE rc IS REF CURSOR;
  3    l_rc rc;
  4    l_object_name t.object_name%TYPE;
  5  BEGIN
  6    FOR i IN 1 .. 20000
  7    LOOP
  8      OPEN l_rc FOR
  9        'SELECT /* test2 */ object_name
 10           FROM t
 11          WHERE object_id = ' || i;
 12      FETCH l_rc INTO l_object_name;
 13      CLOSE l_rc;
 14    END LOOP;
 15  END;
 16  /

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:08.62

SQL> SELECT SUBSTR(sql_text, 56, 25) sql_text
  2       , loads
  3       , parse_calls
  4       , executions
  5       , fetches
  6    FROM v$sql
  7   WHERE sql_text LIKE '%test2%'
  8     AND sql_text NOT LIKE '%v$sql%'
  9     AND sql_text NOT LIKE '%DECLARE%'
 10  ;

SQL_TEXT                LOADS PARSE_CALLS EXECUTIONS FETCHES
----------------------- ----- ----------- ---------- -------
WHERE object_id = 15977     1           1          1       1
WHERE object_id = 15978     1           1          1       1
WHERE object_id = 15979     1           1          1       1
WHERE object_id = 15980     1           1          1       1
WHERE object_id = 15981     1           1          1       1
...                                                        
WHERE object_id = 19996     1           1          1       1
WHERE object_id = 19997     1           1          1       1
WHERE object_id = 19998     1           1          1       1
WHERE object_id = 19999     1           1          1       1
WHERE object_id = 20000     1           1          1       1

4228 개의 행이 선택되었습니다.

경   과: 00:00:01.62